<?php
class Data {
  private $dbh;
  private $theApp;

  function __construct($appHandle = null) {
	$this->theApp = $appHandle;
    $this->setupDBConn();
  }

  function __destruct() {
    $this->teardownDBConn();
  }

  function teardownDBConn() {
    if($this->dbh) mysql_close($this->dbh);
  }

  function setupDBConn() {
    $this->dbh = mysql_connect($this->theApp->config->host,
                               $this->theApp->config->user,
                               $this->theApp->config->pass) or die ("Database currently offline.");
    mysql_select_db($this->theApp->config->db,$this->dbh);
  }
  
  private function getDBH() {
    return $this->dbh;
  }
  
  function getOverAllStatsOffer() {
    $sql = "SELECT MIN(DATE) AS MINDATE, MAX(DATE) AS MAXDATE, " . 
           "SUM(TOTAL) AS SUMM, COUNT(*) AS CNT FROM " . 
           $this->theApp->config->db . ".account_offer WHERE ITEM_TYP = 'O'";
    return $this->singleObjectSelect($sql);
  }

  function getMinMaxDates() {
    $sql = "SELECT MIN(DATE) AS MINDATE, MAX(DATE) AS MAXDATE " . 
           " FROM " . 
           $this->theApp->config->db . ".account_offer";
    return $this->singleObjectSelect($sql);
  }
  
  function getOverAllStatsInvoice() {
    $sql = "SELECT MIN(DATE) AS MINDATE, MAX(DATE) AS MAXDATE, " . 
           "SUM(TOTAL) AS SUMM, COUNT(*) AS CNT FROM " . 
           $this->theApp->config->db . ".account_offer WHERE ITEM_TYP = 'I'";
    return $this->singleObjectSelect($sql);
  }
  
  function getWorkItems() {
    $sql = "SELECT ID, WORK_TEXT FROM " . $this->theApp->config->db . ".works ORDER BY WORK_TEXT ASC";
    return $this->multipleObjectSelect($sql);
  }
  
  function getCustomers() {
    $sql = "SELECT ID, FAMILYNAME, CITY, FIRSTNAME FROM " . $this->theApp->config->db . ".customer ORDER BY FAMILYNAME ASC";
    return $this->multipleObjectSelect($sql);
  }
  
  function getReportData() {
    $sql = "SELECT AO.ID AS AOID, TOTAL, D.UNIT_PRICE AS DUNIT_PRICE, D.AMOUNT AS DAMOUNT, D.DISCOUNT, W.WORK_TEXT, W.MEASURE_UNIT, AO.DATE, " . 
           "C.FAMILYNAME, C.FIRSTNAME, C.TITLE, C.CITY, C.ZIP, AO.ITEM_TYP " . 
           $this->createSelectStatementForReport() . "ORDER BY AO.ITEM_TYP, C.ID";
    return $this->multipleObjectSelect($sql);
  }

  function getTotalSum() {
    $sql = "SELECT DISTINCT(AO.ID) AS AOID " . $this->createSelectStatementForReport();
    $helper = "";
    foreach($this->multipleObjectSelect($sql) as $aoid) {
      $helper .= $aoid->AOID . ",";
    }
    $sql = "SELECT SUM(TOTAL) AS SUMM, COUNT(*) AS CNT FROM " . $this->theApp->config->db . 
           ".account_offer WHERE ID IN (" . substr($helper,0,-1) . ")";
    return $this->singleObjectSelect($sql);
  }
  
  private function createSelectStatementForReport() {
    $from  = $_POST['from'];
    $to    = $_POST['to'];
    $type  = $_POST['type'];
    $cust  = $_POST['customer'];
    $works = $_POST['works'];

    $sql = "FROM " . $this->theApp->config->db . ".account_offer as AO JOIN " . 
                              $this->theApp->config->db . ".customer as C ON AO.CUSTOMER_ID = C.ID JOIN " .
                              $this->theApp->config->db . ".detail as D ON AO.ID = D.ITEM_ID JOIN " .
                              $this->theApp->config->db . ".works as W ON D.WORK_ID = W.ID " .
           "WHERE AO.DATE >= '" . $from . "' AND AO.DATE <= '" . $to . "' ";
    if(!in_array("%", $cust)) {
      $sql .= "AND C.ID IN (".implode(",", $cust).") ";
    }
    if(!in_array("%", $works)) {
      $sql .= "AND W.ID IN (".implode(",", $works).") ";
    }
    if($type != "%") {
      $sql .= "AND AO.ITEM_TYP = '".$type."' ";
    }
    return $sql;
  }

  private function breakWithError($reason) {
    // todo: nicer error handling, logging would be perfect
    $this->theApp->business->errorHandler($reason);
  }


  function formDate($dateString) {
    return date('Y-m-d',strtotime($dateString));
  }

  public function __toString() {
    return "Happy greetings from Data Layer.";
  }

  private function singleInsertStatement($sql_insert) {
    if(mysql_query($sql_insert)) {
      return true;
    }
    else {
      return false;
    }
  }

  private function singleUpdateStatement($sql_update) {
    mysql_query($sql_update);
    if(mysql_rows_affected($this->getDBH()) == 1) {
      return true;
    }
    return false;
  }

  function singleRowSelect($sql_statement) {
	$result = mysql_query($sql_statement,$this->dbh);
	$row = mysql_fetch_row($result);
    return $row;
  }

  function singleObjectSelect($sql_statement) {
    $obj = null;
    $result = mysql_query($sql_statement,$this->dbh);
    if(mysql_num_rows($result) == 1) {
      $obj = mysql_fetch_object($result);
    }
    return $obj;
  }

  function multipleObjectSelect($sql) {
    $resArray = array();
    $result = mysql_query($sql,$this->dbh);
    while($obj = mysql_fetch_object($result)) {
      $resArray[] = $obj;
    }
    return $resArray;
  }

  private function tableCount($table,$focus,$where = "") {
    if($where != "") {
      $where = "WHERE " . $where;
    }
    $sql_count = "SELECT COUNT(" . $focus . ") FROM " . $table . " " . $where;
    $row = $this->singleRowSelect($sql_count);
    return $row[0];
  }

  function lastID() {
    return $this->getLastInsertID();
  }

  private function getLastInsertID() {
//    $row = $this->singleRowSelect("SELECT LAST_INSERT_ID()");
    $obj = $this->singleObjectSelect("SELECT SCOPE_IDENTITY() AS last_insert_id");
    return $obj->last_insert_id;
  }
}
